#!/user/bin/env python
# -*- coding: utf-8 -*-

import sys
import os
import sqlite3
########################################################################

#os.system("export CLASSPATH=`pwd`/sqlitejdbc-v056.jar")

print sys.getdefaultencoding()
########################################################################

def getListEstablishment():
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(estabelecimento) from info")
	
	listEstablishment = []
	
	for row in cursor:
		listEstablishment.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listEstablishment
	#sys.exit(0)

def getListYears():
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(ano_lectivo) from info")
	
	listYears = []
	
	for row in cursor:
		listYears.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listYears

def getListCategories():
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(categoria) from info")
	
	listCategories = []
	
	for row in cursor:
		listCategories.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listCategories
	
def getListDegrees():
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(grau) from info")
	
	listDegrees = []
	
	for row in cursor:
		listDegrees.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listDegrees
	
def getFirstDefaultQuery(establishment, year):
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	queryToExecute = "select distinct(nome) from info where estabelecimento = '" + establishment + "' and ano_lectivo = '" + str(year) + "'"
	cursor.execute(queryToExecute)
	
	listResult = []
	
	for row in cursor:
		listResult.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listResult
	
def getSecondDefaultQuery(establishment, year, categorie):
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	queryToExecute = ("select distinct(nome) from info where estabelecimento = '" 
	+ establishment + "' and ano_lectivo = '" + str(year) + "' and categoria= '" + 
	categorie + "'")
	cursor.execute(queryToExecute)
	
	listResult = []
	
	for row in cursor:
		listResult.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listResult

def getThirdDefaultQuery(establishment, year, degree):
	
	
	dbConn = sqlite3.connect("Rebides.db")
    
	cursor = dbConn.cursor()
	queryToExecute = ("select distinct(nome) from info where estabelecimento = '" 
	+ establishment + "' and ano_lectivo = '" + str(year) + "' and grau= '" + 
	degree + "'")
	cursor.execute(queryToExecute)
	
	listResult = []
	
	for row in cursor:
		listResult.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listResult

########################################################################

########################################################################
